using Dapper;
using System;
using SmartLink.Domain.Entity;
using SmartLink.IRepository;
using SmartLink.Repository.Base;
using System.Collections.Generic;
using SmartLink.Domain;
using SmartLink.Domain.ExpandEntity.Base;
using SmartLink.EnumLibrary;
using System.Text;
using SmartLink.Infrastructure;
using System.Data.Common;
using SmartLink.Domain.ExpandEntity;
using SmartLink.Code;

namespace SmartLink.Repository
{
    public class SYS_UserRepo : RepositoryBase<SYS_User>, ISYS_UserRepo
    {
        #region Checked

        public bool ExistAccountName(string accountName)
        {
            var sql = "SELECT EXISTS(SELECT * FROM SYS_User WHERE AccountName=@AccountName AND IsDel=0)";
            using (var conn = CreateConn())
            {
                return conn.ExecuteScalar<bool>(sql, new { AccountName = accountName });
            }
        }

        public bool ExistUserEmail(string userEmail)
        {
            var sql = "SELECT EXISTS(SELECT * FROM SYS_User WHERE UserEmail=@UserEmail AND IsDel=0)";
            using (var conn = CreateConn())
            {
                return conn.ExecuteScalar<bool>(sql, new { UserEmail = userEmail });
            }
        }

        public SYS_User GetUserByAccount(string account)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = "SELECT * FROM SYS_User WHERE (AccountName=@Account OR UserEmail=@Account OR UserMobile=@Account) AND IsDel=false";
                return conn.QueryFirstOrDefault<SYS_User>(sql, new { Account = account });
            }
        }

        public dynamic GetUserCertificate(int userId)
        {
            var sql = @"SELECT u.AccountName,u.NickName,u.ID UserId,u.UserMobile,u.UserEmail,u.UserStatus,
							 uto.OID,uto.OD_ID,org.OrganizationName,orgDept.DepartmentName,
                             utr.RID,role.RoleName, u.HasUpdatedPwdSelf,u.UserType
						FROM SYS_User u  
                        left JOIN BIZ_UserToOrganization uto
                        ON u.ID = uto.UserID 
                        LEFT JOIN BIZ_Organization org 
                        ON uto.OID = org.ID 
                        LEFT JOIN PER_UserToRole utr 
                        ON u.ID = utr.UserID
                        LEFT JOIN BIZ_OrganizationDepartment orgDept 
                        ON uto.OD_ID = orgDept.ID
                        LEFT JOIN PER_RoleGroup role 
                        ON utr.RID = role.ID
                        where u.ID=@UserId ";

            using (var conn = DapperHelper.GetDefaultConnection())
            {
                return conn.QueryFirstOrDefault<dynamic>(sql, new { UserId = userId });
            }
        }

        public IEnumerable<dynamic> GetPaged(int? oid, int? odid, UserStatusEnum? userStatus, string keyValue, int curUserId, Pagination pagination, bool isSuperManager = false, bool isorgManager = false)
        {
            var selectSql = @"SELECT u.ID, u.AccountName, u.NickName, u.UserMobile, u.UserPhone, od.DepartmentName, rg.RoleName,
                            u.UserStatus, u.CreateTime, c.NickName AS Creator, u.LastLoginTime, uto.OID, o.ROID,
                            uto.OD_ID ODOD ";
            var sql = new StringBuilder(@" FROM SYS_User u
                                     INNER JOIN BIZ_UserToOrganization uto ON u.ID = uto.UserID
                                     INNER JOIN BIZ_Organization o ON o.ID = uto.OID
                                      LEFT JOIN BIZ_OrganizationDepartment od ON uto.OD_ID=od.ID
                                      LEFT JOIN PER_UserToRole utr ON u.ID = utr.UserID 
                                      LEFT JOIN PER_RoleGroup rg ON rg.ID=utr.RID
                                      LEFT JOIN SYS_User c ON u.Creator=c.ID
                                          WHERE u.IsDel=false");
            if (oid.HasValue)
                sql.Append(" AND uto.OID=@OID ");
            if (odid.HasValue)
                sql.Append(" AND uto.OD_ID=@ODID ");
            if (userStatus.HasValue)
                sql.Append(" AND u.UserStatus=@UserStatus ");
            if (!string.IsNullOrWhiteSpace(keyValue))
                sql.Append(" AND (u.AccountName LIKE @KeyWord OR u.NickName LIKE @KeyWord OR u.UserPhone LIKE @KeyWord OR u.UserMobile LIKE @KeyWord) ");

            //角色等级可视范围限制
            if (!isSuperManager && !isorgManager)
                sql.AppendFormat($" AND (NOT (rg.RoleName IN('{ConstData.SuperManagerRoleName}', '{ConstData.OrgManagerRoleName}') AND rg.IsBuildin) OR rg.ID IS NULL) ");
            else if (!isSuperManager)
                sql.AppendFormat($" AND (NOT (rg.RoleName IN('{ConstData.SuperManagerRoleName}') AND rg.IsBuildin) OR rg.ID IS NULL) ");

            var filterSql = new DataAuthorizeFilter(ModuleCodeEnum.UserManage).WrapSql(selectSql + sql.ToString());
            using (var conn = CreateConn())
            {
                var listSql = filterSql + " ORDER BY CreateTime DESC " + pagination.ToLimitString();
                var param = new { OID = oid, ODID = odid, UserStatus = (int?)userStatus, KeyWord = $"%{keyValue}%" };
                var list = conn.Query<dynamic>(listSql, param);
                if (pagination.IsSearchTotal)
                    pagination.TotalCount = conn.ExecuteScalar<int>($"SELECT COUNT(*) FROM ({filterSql}) a", param);
                return list;
            }
        }

        public dynamic GetUserInfo(int id)
        {
            var sql = @"SELECT u.ID, u.AccountName, u.NickName, u.UserMobile, u.UserPhone, uto.OD_ID AS DeptId,
                               u.JobNo, u.Sex, u.UserEmail, u.QQ, u.Birthday, u.WeChat, u.UserStatus,
                               uto.OID, o.ROID
                          FROM SYS_User u
                    INNER JOIN BIZ_UserToOrganization uto ON u.ID = uto.UserID
                    INNER JOIN BIZ_Organization o ON o.ID = uto.OID
                         WHERE u.ID=@UserId";
            using (var conn = CreateConn())
            {
                return conn.QuerySingleOrDefault<dynamic>(sql, new { UserId = id });
            }
        }

        public void Delete(int id)
        {
            using (var conn = CreateConn())
            {
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        var param = new { UserId = id };
                        conn.Execute("UPDATE SYS_User SET IsDel=1 WHERE ID=@UserId", param, tran);
                        conn.Execute("DELETE FROM BIZ_UserToOrganization WHERE UserID=@UserId", param, tran);
                        conn.Execute("DELETE FROM PER_UserToRole WHERE UserID=@UserId", param, tran);
                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        #endregion


        public int GetUserIdByName(string username)
        {
            int result = 0;
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = " select ID from SYS_User where (AccountName = @UserName or NickName=@UserName) and isDel=0";
                result = conn.QueryFirstOrDefault<int>(sql, new { UserName = username });
                conn.Close();
            }
            return result;
        }

        public void DeleteUserByOrgId(int orgId)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = $@" update sys_user set IsDel=1 where id in (select UserID id from biz_usertoorganization 
                             where OID={orgId})";
                int count = conn.Execute(sql);
                conn.Close();
            }
        }

        public IList<dynamic> GetUserList(string odidStr)
        {
            IList<dynamic> results = null;
            string whereStr = " where 1=1 and u.isDel=0 ";
            if (!string.IsNullOrEmpty(odidStr))
                whereStr += $@"and  od_id in ({odidStr})";
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = string.Format(@" select *  from SYS_User u inner join  BIZ_UserToOrganization uto 
                                           on u.ID = uto.UserID
                                           left join PER_UserToRole utr 
                                           on u.ID = utr.UserID 
                                           left join PER_RoleGroup rg 
                                           on rg.ID=utr.RID  {0}
                                           order by u.CreateTime desc", whereStr);

                results = conn.Query<dynamic>(sql).AsList();
                conn.Close();
            }
            return results;
        }

        public IEnumerable<UserSelectItemExpand> GetUserSelectList(int orgId, bool isDept)
        {
            var sql = @"SELECT u.ID, u.NickName, u.UserMobile Mobile, rg.RoleName
                          FROM SYS_User u
                    INNER JOIN BIZ_UserToOrganization uto ON uto.UserID=u.ID
                     LEFT JOIN PER_UserToRole utr ON utr.UserID=u.ID
                     LEFT JOIN PER_RoleGroup rg ON rg.ID=utr.RID
                         WHERE 1=1";
            if (isDept)
                sql += $" AND uto.OD_ID={orgId}";
            else
                sql += $" AND uto.OD_ID IS NULL AND uto.OID={orgId}";
            using (var conn = CreateConn())
            {
                return conn.Query<UserSelectItemExpand>(sql);
            }
        }

        public SYS_User GetUserByOpenID(string openId)
        {
            using (var conn = DapperHelper.GetDefaultConnection())
            {
                var sql = $@" select * from sys_user where IsDel=0 and openid = '{openId}'";
                SYS_User user= conn.QueryFirstOrDefault<SYS_User>(sql);
                conn.Close();
                return user;
            }
        }
    }
}
